﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;

namespace BLL
{
   public class ClsTinhChatVatLyBus
    {
       public ClsTinhChatVatLyInfo Info = new ClsTinhChatVatLyInfo();
       public DataTable SelectMau()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT MaMau,TenMau FROM DuLieuMau,TTCHUNG WHERE DuLieuMau.MaGK=TTCHUNG.MaGK AND TTCHUNG.Tinh='"+Info.MaTinh+"'";

            if (oConnection.ExecuteSelect(strSql))
            {
                return oConnection.ODataTable;
            }
            else return null;
        }
        public DataTable SelectThiNghiem()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @"SELECT MaThiNghiem FROM TTThiNghiemMau";

            if (oConnection.ExecuteSelect(strSql))
            {
                return oConnection.ODataTable;
            }
            else return null;
        }

        public DataTable SelectOne()
        {
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @" SELECT TTCHUNG.TenGK,DuLieuMau.TenMau,TinhChatVatLy.MaThiNghiem,TinhChatVatLy.NhietDo,TinhChatVatLy.MauSac,TinhChatVatLy.MuiVi,TinhChatVatLy.DoDuc,TinhChatVatLy.DoDanDien,TinhChatVatLy.TDS,TinhChatVatLy.TSS,TinhChatVatLy.DoPhongXaAnfa,TinhChatVatLy.DoPhongXaBeta
                              FROM TTCHUNG,DuLieuMau,TinhChatVatLy
                              WHERE TTCHUNG.MaGK= DuLieuMau.MaGK
                              AND DuLieuMau.MaMau=TinhChatVatLy.MaMau 
                              AND TTCHUNG.MaGK like'%" + Info.MaGK +
                              "%'AND TTCHUNG.Tinh='" + Info.MaTinh + "'";

            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public DataTable SelectAll()
        {//,,
            ClsDataAccess oConnection = new ClsDataAccess();
            string strSql = @" SELECT TTCHUNG.TenGK,DuLieuMau.TenMau,TinhChatVatLy.MaThiNghiem,TinhChatVatLy.NhietDo,TinhChatVatLy.MauSac,TinhChatVatLy.MuiVi,TinhChatVatLy.DoDuc,TinhChatVatLy.DoDanDien,TinhChatVatLy.TDS,TinhChatVatLy.TSS,TinhChatVatLy.DoPhongXaAnfa,TinhChatVatLy.DoPhongXaBeta
                              FROM TTCHUNG,DuLieuMau,TinhChatVatLy
                              WHERE TTCHUNG.MaGK= DuLieuMau.MaGK
                              AND DuLieuMau.MaMau=TinhChatVatLy.MaMau
                              AND TTCHUNG.Tinh='" + Info.MaTinh + "'";
            //string strSql = @"SELECT * FROM HamLuongViSinh";
            if (oConnection.ExecuteSelect(strSql))
            {
                oConnection.ODataTable.Columns.Add("STT");
                int i = 0;
                foreach (DataRow oDRow in oConnection.ODataTable.Rows)
                {
                    oDRow["STT"] = (i + 1);
                    i++;
                }
                return oConnection.ODataTable;
            }
            else return null;
        }
        public bool Them()
        {
            string strSql = "EXECUTE InsertTinhChatVatLy '" + Info.MaMau + "','" + Info.MaThiNghiem + "','" + Info.NhietDo + "','" + Info.MauSac + "',N'" + Info.MuiVi + "','" + Info.DoDuc + "','" + Info.DoDanDien + "','" + Info.TDS + "','" + Info.TSS + "','" + Info.DoPhongXaAnfa + "','" + Info.DoPhongXaBeta + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));
        }

        public bool Sua()
        {
            string strSql = "EXECUTE UpdateTinhChatVatLy '" + Info.MaMau + "','" + Info.MaThiNghiem + "','" + Info.NhietDo + "','" + Info.MauSac + "',N'" + Info.MuiVi + "','" + Info.DoDuc + "','" + Info.DoDanDien + "','" + Info.TDS + "','" + Info.TSS + "','" + Info.DoPhongXaAnfa + "','" + Info.DoPhongXaBeta + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));

        }

        public bool Xoa()
        {
            string strSql = "EXECUTE DeleteTinhChatVatLy'" + Info.MaMau + "'";
            ClsDataAccess oConnection = new ClsDataAccess();
            return (oConnection.ExecuteStoredProcedure(strSql));

        }
    }
}
